/*
 * Sales_Model.java
 * Final Project Example
 * Created on Oct 5, 2014, 03:34:13 PM
 * by Fairul Filza with Team FA_PemrogrammanLanjut
 */

package Models;

// <editor-fold defaultstate="collapsed" desc="Import Code">
import Database.DvdsRetail_Connection;
import Interfaces.ModelsProcess_Interface;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
// </editor-fold>

/** @author PemrogrammanLanjut_LABIV_TI_05~09 */
public class Sales_Model implements ModelsProcess_Interface {
    private String SalesId;
    private String DateSale;
    private long Total;
    private DvdsRetail_Connection Connex;
    private String[][] DataRecord;

    // <editor-fold defaultstate="collapsed" desc="Constructor Code">
    public Sales_Model() {
        Connex = new DvdsRetail_Connection();
    }

    public Sales_Model(String SalesId, String DateSale, long Total) {
        Connex = new DvdsRetail_Connection();
        this.SalesId = SalesId;
        this.DateSale = DateSale;
        this.Total = Total;
    }

    public Sales_Model(String SalesId) {
        Connex = new DvdsRetail_Connection();
        this.SalesId = SalesId;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="Getter and Setter Code">
    public String getDateSale() {
        return DateSale;
    }

    public void setDateSale(String DateSale) {
        this.DateSale = DateSale;
    }

    public String[][] getDataRecord() {
        return DataRecord;
    }

    public void setDataRecord(String[][] DataRecord) {
        this.DataRecord = DataRecord;
    }

    public String getSalesId() {
        return SalesId;
    }

    public void setSalesId(String SalesId) {
        this.SalesId = SalesId;
    }

    public long getTotal() {
        return Total;
    }

    public void setTotal(long Total) {
        this.Total = Total;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="Method Code">
    @Override
    public void showDataSet() {
        String Query;
        int i;

        Connex.Connection();
        try {
            Query = "SELECT a.SalesId, a.DateSale, ("
                    + "SELECT sum(Quantity) FROM DvdsSalesDetail WHERE SalesId = a.SalesId"
                    + ") AS Qty, a.Total FROM DvdsSales a";
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            ResultSet List = Stat.executeQuery(Query);
            List.last();
            DataRecord = new String[List.getRow()][4];
            List.beforeFirst();
            i = 0;
            while (List.next()) {
                DataRecord[i][0] = List.getString(1);
                DataRecord[i][1] = List.getString(2);
                DataRecord[i][2] = List.getString(3);
                DataRecord[i][3] = List.getString(4);
                i++;
            }
            List.close();
            Stat.close();
        } catch(Exception Ex) {
            System.out.println("[ERROR] - Sales_Model.showDataSet() " + Ex.getMessage().toString());
        }
    }

    @Override
    public void showSelectedDataSet(String Value, String Key) {
        String Query;
        String Parse;

        Connex.Connection();
        try {
            Query = "SELECT SalesId, DateSale, Total FROM DvdsSales ";
            if(Key.equals("SalesId")) {
                Parse = "WHERE SalesId = '" + Value + "'";
            } else if(Key.equals("DateSale")) {
                Parse = "WHERE DateSale = '" + Value + "'";
            } else if(Key.equals("Total")) {
                Parse = "WHERE Total = '" + Value + "'";
            } else {
                Parse = "";
            }
            Query = Query + Parse;
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            ResultSet List = Stat.executeQuery(Query);
            SalesId = null;
            DateSale = null;
            Total = 0;
            while (List.next()) {
                SalesId = List.getString(1);
                DateSale = List.getString(2);
                Total = List.getLong(3);
            }
            List.close();
            Stat.close();
        } catch(Exception Ex) {
            System.out.println("[ERROR] - Sales_Model.showSelectedDataSet() " + Ex.getMessage().toString());
        }
    }

    @Override
    public boolean addDataValue() {
        String Query;

        Connex.Connection();
        try {
            Query = "INSERT INTO DvdsSales VALUES('" + SalesId + "','" + DateSale + "','" + Total + "')";
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            Stat.executeUpdate(Query);
            Stat.close();
            return true;
        } catch (SQLException Ex) {
            System.out.println("[ERROR] - Sales_Model.addDataValue() " + Ex.getMessage().toString());
            return false;
        }
    }

    @Override
    public boolean editDataValue() {
        String Query;

        Connex.Connection();
        try {
            Query = "UPDATE DvdsSales SET DateSale = '" + DateSale + "', Total = '" + Total + "' "
                    + "WHERE SalesId = '" + SalesId + "'";
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            Stat.executeUpdate(Query);
            Stat.close();
            return true;
        } catch (SQLException Ex) {
            System.out.println("[ERROR] - Sales_Model.editDataValue() " + Ex.getMessage().toString());
            return false;
        }
    }

    @Override
    public boolean subDataValue() {
        String Query;

        Connex.Connection();
        try {
            Query = "DELETE FROM DvdsSales WHERE SalesId = '" + SalesId + "'";
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            Stat.executeUpdate(Query);
            Stat.close();
            return true;
        } catch (SQLException Ex) {
            System.out.println("[ERROR] - Sales_Model.subDataValue() " + Ex.getMessage().toString());
            return false;
        }
    }

    @Override
    public String genIdDataValue() {
        String Query;
        String Result;
        int hasil;

        Connex.Connection();
        try {
            Result = null;
            Query = "SELECT SalesId FROM DvdsSales ORDER BY SalesId DESC LIMIT 1";
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            ResultSet List = Stat.executeQuery(Query);
            while (List.next()) {
                Result = List.getString(1).trim();
            }
            List.close();
            Stat.close();

            if (!Result.equals("")) {
                hasil = Integer.parseInt(Result.substring(3)) + 1;
                Result = String.valueOf(hasil);
                if (Result.length() == 1) {
                    Result = "100000" + Result;
                } else if (Result.length() == 2) {
                    Result = "10000" + Result;
                } else if (Result.length() == 3) {
                    Result = "1000" + Result;
                } else if (Result.length() == 3) {
                    Result = "100" + Result;
                } else if (Result.length() == 3) {
                    Result = "10" + Result;
                }
            } else {
                Result = "1000001";
            }
            return Result;
        } catch (Throwable Ex) {
            System.out.println("[ERROR] - Sales_Model.genIdDataValue() " + Ex.getMessage());
            return "1000001";
        }
    }

    public String showGrandTotal() {
        String GrandTotal;

        try {
            String Query = "SELECT sum(Price) AS Price FROM DvdsSalesDetail "
                         + "WHERE SalesId = '" + SalesId + "'";
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            ResultSet List = Stat.executeQuery(Query);
            GrandTotal = null;
            while(List.next()) {
                GrandTotal = List.getString(1).trim();
            }
            Query = "UPDATE DvdsSales SET DateSale = '" + DateSale + "', Total = '" + GrandTotal + "' "
                    + "WHERE SalesId = '" + SalesId + "'";
            System.out.println("[QUERY] - " + Query);
            Stat = Connex.getConnector().createStatement();

            List.close();
            Stat.close();
            return GrandTotal;
        } catch (Exception Ex) {
            System.out.println("[ERROR] - Sales_Model.showGrandTotal at " + Ex.getMessage());
            return "0";
        }
    }

    public void showFoundedDataSet(String Key) {
        String Query;
        int i;

        Connex.Connection();
        try {
            Query = "SELECT a.SalesId, a.DateSale, ("
                    + "SELECT sum(Quantity) FROM DvdsSalesDetail WHERE SalesId = a.SalesId"
                    + ") AS Qty, a.Total FROM DvdsSales a "
                    + "WHERE month(a.DateSale) = '" + Key.substring(5) + "' AND year(a.DateSale) = '" + Key.substring(0, 4) + "'";
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            ResultSet List = Stat.executeQuery(Query);
            List.last();
            DataRecord = new String[List.getRow()][4];
            List.beforeFirst();
            i = 0;
            while (List.next()) {
                DataRecord[i][0] = List.getString(1);
                DataRecord[i][1] = List.getString(2);
                DataRecord[i][2] = List.getString(3);
                DataRecord[i][3] = List.getString(4);
                i++;
            }
            List.close();
            Stat.close();
        } catch(Exception Ex) {
            System.out.println("[ERROR] - Sales_Model.showFoundedDataSet() " + Ex.getMessage().toString());
        }
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="Tester Code">
//    public void methodTester(int flag) {
//        switch(flag) {
//            case 0:
//                showDataSet();
//                System.out.println("Result = " + this.DataRecord[0][1]);
//                break;
//            case 1:
//                showSelectedDataSet("1000001", "SalesId");
//                System.out.println("Result = " + this.DateSale);
//                break;
//            case 2:
//                System.out.println("Result = " + addDataValue());
//                break;
//            case 3:
//                System.out.println("Result = " + editDataValue());
//                break;
//            case 4:
//                System.out.println("Result = " + subDataValue());
//                break;
//            case 5:
//                System.out.println("Result = " + genIdDataValue());
//                break;
//            case 6:
//                System.out.println("Result = " + showGrandTotal());
//                break;
//        }
//    }
//
//    public static void main(String[] args) {
//        Sales_Model Me = new Sales_Model("1000001", "2014/10/05", 500000);
////        Sales_Model Me = new Sales_Model();
//        Me.methodTester(1);
//    }
    // </editor-fold>
}
// End of Code